<?php
namespace Common\Model;
use Think\Model;
class TableModel {
    public $sql = '',$table = '';
    public function getInfo($tb ='')
    {
        $Model = M();
        $data = $Model->query("SHOW FULL COLUMNS FROM $tb;");
        $return = array();
        $i = 0;
        foreach ($data as $row) {
            preg_match('~^([^( ]+)(?:\\((.+)\\))?( unsigned)?( zerofill)?$~', $row["type"], $match);
            $return[$row["field"]] = array(
                "pos" => $i,
                "field" => $row["field"],
                "full_type" => $row["type"],
                "type" => $match[1],
                "length" => $match[2],
                "unsigned" => ltrim($match[3] . $match[4]),
                "default" => ($row["default"] != "" || preg_match("~char|set~", $match[1]) ? $row["default"] : null),
                "isnull" => $row["null"],
                "auto_increment" => ($row["extra"] == "auto_increment"),
                "on_update" => (preg_match('~^on update (.+)~i', $row["extra"], $match) ? $match[1] : ""), //! available since MySQL 5.1.23
                "collation" => $row["collation"],
                "privileges" => array_flip(preg_split('~, *~', $row["privileges"])),
                "comment" => $row["comment"],
                "primary" => $row["key"],
                "table" => $tb,
            );
            $i ++;
        }
        return $return;
    }

    

    public function saveFieldDef($tb ='',$field = '',$def = '')
    {
        $f = $this->getFieldInfo($tb,$field);
        if(!$f) return;
        if ($def) $_def = " DEFAULT ".$def;
        $null = " NOT NULL ";
        if ($f['isnull'] == 'YES') $null = ' NULL ';
        $sql = "ALTER TABLE `".$tb."`  MODIFY COLUMN `".$f['field']."` ".$f['full_type'].$null.$_def." ".$this->getFieldComment($f).";";
        // print_r($sql);exit();
        $Model = M();
        $Model->execute($sql);
    }

    public function saveFieldIsNull($tb ='',$field = '',$value = '')
    {
        $f = $this->getFieldInfo($tb,$field);
        if(!$f) return;
        $null = " NOT NULL ";
        if ($value == 'YES') $null = ' NULL ';
        $sql = "ALTER TABLE `".$tb."`  MODIFY COLUMN `".$f['field']."` ".$f['full_type'].$null." ".$this->getFieldComment($f).";";
        // print_r($sql);exit();
        $Model = M();
        $Model->execute($sql);
    }

    public function saveFieldLength($tb ='',$field = '',$len = '')
    {
        $f = $this->getFieldInfo($tb,$field);
        if(!$f) return;
        $null = " NOT NULL ";
        if ($f['isnull'] == 'YES') $null = ' NULL ';
        $sql = "ALTER TABLE `".$tb."`  MODIFY COLUMN `".$f['field']."` ".$f['type']."(".$len.") ".$null." ".$this->getFieldComment($f)." ;";
        // print_r($sql);exit();
        $Model = M();
        $Model->execute($sql);
    }

    public function saveFieldComment($tb ='',$field = '',$comment = '')
    {
        $f = $this->getFieldInfo($tb,$field);
        if(!$f) return;
        $null = " NOT NULL ";
        if ($f['isnull'] == 'YES') $null = ' NULL ';
        $sql = "ALTER TABLE `".$tb."`  MODIFY COLUMN `".$f['field']."` ".$f['full_type'].$null." COMMENT '".$comment."';";
        $Model = M();
        $Model->execute($sql);
    }


    public function getFieldIsnull($fInfo ='')
    {
        if(!$fInfo) return;
        $null = " NOT NULL ";
        if ($fInfo['isnull'] == 'YES') $null = ' NULL ';
        return $null;
    }

    public function getFieldComment($fInfo ='')
    {
        if(!$fInfo) return;
        if ($fInfo['comment']);
        return " COMMENT '".$fInfo['comment']."' ";
    }

    public function saveFieldMove($tb ='',$field = '',$arrow = '')
    {


        $fields = $this->getInfo($tb);
        $f = '';$m = 0;
        $up = '';$down = '';$mi ='';

        // while(each($fields)){
        //     $v = current($fields);
        //     print_r(current($fields));
        // }
        $keys = array_keys($fields);


        for ($i=0; $i < count($fields); $i++) {
            if (strtolower($fields[$keys[$i]]['field']) == strtolower($field)) {
                $mi = $fields[$keys[$i]];
                $m = $i;
                break;
            }
        }


        $up = $fields[$keys[$m-1]];
        $down = $fields[$keys[$m+1]];


        $sql = " ALTER TABLE `".$tb."` ";
        if ($arrow == 'moveUp') {
            $upup = $fields[$keys[$m-2]];
            if (!$upup) {
                $sql .= ' MODIFY COLUMN `'.$mi['field'].'` '.$mi['full_type'].' '.$this->getFieldIsnull($mi).$this->getFieldComment($mi).' FIRST ,';
            }else{
                $sql .= ' MODIFY COLUMN `'.$mi['field'].'` '.$mi['full_type'].' '.$this->getFieldIsnull($mi).$this->getFieldComment($mi).' AFTER `'.$upup['field'].'` ,';

            }
            $sql .= ' MODIFY COLUMN `'.$up['field'].'` '.$up['full_type'].' '.$this->getFieldIsnull($up).$this->getFieldComment($up).' AFTER `'.$mi['field'].'` ;';

        }elseif ($arrow == 'moveDown') {
            $sql .= ' MODIFY COLUMN `'.$mi['field'].'` '.$mi['full_type'].' '.$this->getFieldIsnull($mi).$this->getFieldComment($mi).' AFTER `'.$down['field'].'` ,';
            $sql .= ' MODIFY COLUMN `'.$down['field'].'` '.$down['full_type'].' '.$this->getFieldIsnull($down).$this->getFieldComment($down).' AFTER `'.$up['field'].'` ;';
        }
        print_r($sql);exit();
        $Model = M();
        $Model->execute($sql);
    }


    public function getFieldInfo($tb ='',$field = '')
    {
        $fields = $this->getInfo($tb);
        foreach ($fields as $k) {
            if (strtolower($k['field']) == strtolower($field)) {
                return $k;
            }
        }
        return;
    }

    public function getTables()
    {
        $Model = M();
        $tables = $Model->query("select * from information_schema.tables where table_schema='".C('DB_NAME')."' and table_type='base table';");
        return $tables;
    }

    public function add($data ='')
    {
        extract($data);
        $str  = '';
        if ($isnull == 'YES')
            $isnull = " NULL ";
        else
            $isnull = " NOT NULL";
        if ($default) $_def = " DEFAULT ".$default;
        if ($comment) $_comment = " COMMENT '".$comment."' ";
        
        $str = "ADD COLUMN `".$field."` ".$type."(".$length.") ".$isnull.$_comment.$_def." AFTER `".$after."`";
        $this->sql .= $str;
        return $this;
    }

    public function deleteField($tb ='',$field)
    {
        $sql = 'ALTER TABLE `'.$tb.'` DROP COLUMN `'.$field.'`;';
        $Model = M();
        $Model->execute($sql);
    }


    public function exec()
    {
        $this->sql .=';';
        $this->sql = str_replace(",;", ";", $this->sql);
        $this->sql = str_replace(array("/r/n", "/r", "/n"), "", $this->sql);
        // print_r($this->sql);exit();
        $Model = M();
        $r = $Model->execute($this->sql);
        $this->sql ='';
        $this->table = '';
    }

    public function drop($name='')
    {
        $this->sql .= ' DROP COLUMN `'.$name.'`';
        return $this;
    }

    public function table($name='')
    {
        # code...
    }

    public function shiftUp($name = '')
    {
        $info = $this->getInfo($this->table);
        $i = -1;
        foreach ($info as $k) {
            if ($k['field'] == $name) {
                $i++;
                break;
            }
            $i++;
        }



        $fir = array_slice($info,$i-2,1);
        $fir = current($fir);
        $sce = array_slice($info,$i,1);
        $sce = current($sce);
        $thi = array_slice($info,$i-1,1);
        $thi = current($thi);

        if ($i == 1){
            $this->modify($sce['field'])->first()->modify($thi['field'])->after($sce['field'])->exec();
        }else{
            $this->modify($sce['field'])->after($fir['field'])->modify($thi['field'])->after($sce['field'])->exec();
        }
    }

    public function shiftDown($name = '')
    {
        $info = $this->getInfo($this->table);
        $i = -1;
        foreach ($info as $k) {
            if ($k['field'] == $name) {
                $i++;
                break;
            }
            $i++;
        }


        $fir = array_slice($info,$i-1,1);
        $fir = current($fir);
        $sce = array_slice($info,$i,1);
        $sce = current($sce);
        $thi = array_slice($info,$i+1,1);
        $thi = current($thi);

        if ($i == 0){
            $this->modify($thi['field'])->first()->modify($sce['field'])->after($thi['field'])->exec();
        }else{
            $this->modify($thi['field'])->after($fir['field'])->modify($sce['field'])->after($thi['field'])->exec();
        }
    }


    public function alter($name='')
    {
        $this->table = $name;
        $this->sql = "ALTER TABLE `".$name."` ";
        return $this;
    }



    public function modify($name='')
    {
        $s = $this->field($name);
        if ($s) $this->sql .= ' MODIFY '.$s;
        return $this;
    }

    public function after($name='')
    {
        if ($name) 
            $this->sql .= " AFTER `".$name."` ,";
        else
            $this->sql .= " ,";
        
        return $this;
    }

    public function first()
    {
        $this->sql .= " FIRST ,";
        return $this;
    }

    public function field($name='')
    {

        $f = $this->getFieldInfo($this->table,$name);
        $str = '';
        if ($f['field']) 
            $str = 'COLUMN `'.$name.'` '.$f['full_type'];
        if ($f['isnull'] == 'NO') {
            $str .= ' NOT NULL ';
        }else{
            $str .= ' NULL ';
        }
        if ($f['comment']) {
            $str .= " COMMENT '".$f['comment']."' ";
        }
        return $str;
    }
}
